﻿using MiniFox.Data.Mapping;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Reflection;
using System.Text;

namespace MiniFox.Data.NativeClient
{
    /// <summary>
    /// 
    /// </summary>
    /// <typeparam name="TEntity"></typeparam>
    public abstract class NativeDataOperation<TEntity> : NativeDataCommand<TEntity>, IDataOperation<TEntity> where TEntity : class
    {
        /// <summary>
        /// 
        /// </summary>
        /// <param name="database"></param>
        public NativeDataOperation(Database database) : base(database)
        {
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="incrementParameters"></param>
        /// <returns></returns>
        protected abstract string CreateAutoIncrementOutputExpression(IDictionary<string, string> incrementParameters);
        //{
        //    string sqlstr = string.Empty;
        //    switch (this.Database.ProviderName)
        //    {
        //        case DatabaseUtil.SQLSERVER_PROVIDER:
        //            foreach (var p in incrementParameters)
        //            {
        //                sqlstr += $" SET {p.Value}=SCOPE_IDENTITY();";
        //            }
        //            break;
        //        case DatabaseUtil.OLEDB_PROVIDER:
        //        case DatabaseUtil.ODBC_PROVIDER:
        //            foreach (var p in incrementParameters)
        //            {
        //                sqlstr += $" SElECT @@IDENTITY INTO {p.Value};";
        //            }
        //            break;
        //        case DatabaseUtil.OMDAC_PROVIDER:
        //        case DatabaseUtil.ODAC_PROVIDER:
        //        case DatabaseUtil.ORACLE_PROVIDER:
        //            Type type = typeof(TEntity);
        //            foreach (var p in incrementParameters)
        //            {
        //                PropertyInfo property = type.GetProperty(p.Key);
        //                IncrementSequenceAttribute sequence = Attribute.GetCustomAttribute(property, typeof(IncrementSequenceAttribute)) as IncrementSequenceAttribute;
        //                if (sequence == null || string.IsNullOrWhiteSpace(sequence.SequenceName))
        //                    continue;

        //                sqlstr += $" SELECT {this.FormatDatabaseObject(sequence.SequenceName)}.CURRVAL INTO {p.Value} FROM DUAL;";
        //            }
        //            //add "begin...end;" block for oracle
        //            sqlstr += $"BEGIN {sqlstr} END;";
        //            break;
        //    }

        //    return sqlstr;
        //}


        /// <summary>
        /// 
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public virtual int ExecuteInsert(TEntity entity)
        {
            int rowcount = 0;
            string sqlstr = null;
            Dictionary<string, object> paraDict = new Dictionary<string, object>();
            try
            {
                ITableMapping tableMapping = TableMapping.GetTableMapping<TEntity>();
                string tableName = FormatDatabaseObject(tableMapping.SourceTable);
                List<string> cols = new List<string>();
                List<string> parNames = new List<string>();
                List<DbParameter> parameters = new List<DbParameter>();
                Type type = typeof(TEntity);
                Dictionary<string, string> incrementParameters = new Dictionary<string, string>();

                foreach (var col in tableMapping.ColumnMappings)
                {
                    ColumnMapping colmapping = (ColumnMapping)col;
                    string propertyName = colmapping.DataSetColumn;
                    string paramName = this.Database.ParameterPrefix + propertyName;
                    PropertyInfo property = type.GetProperty(propertyName);
                    DbParameter par;
                    if (!colmapping.AutoIncrement)
                    {
                        object val = property.GetValue(entity);
                        par = this.Database.CreateParameter(paramName, propertyName, property.PropertyType, System.Data.ParameterDirection.Input);
                        par.Value = val;
                        cols.Add(colmapping.SourceColumn);
                        parNames.Add(par.ParameterName);
                    }
                    else
                    {
                        par = this.Database.CreateParameter(paramName, propertyName, property.PropertyType, System.Data.ParameterDirection.Output);
                        incrementParameters.Add(propertyName, paramName);
                    }
                    paraDict.Add(par.ParameterName, par.Value);
                    parameters.Add(par);
                }
                List<string> strStatements = new List<string>();
                strStatements.Add(string.Format(DatabaseUtil.INSERT_COMMAND, tableName, string.Join(",", cols.ToArray()), string.Join(",", parNames.ToArray())));
                if (incrementParameters.Count > 0)
                {
                    strStatements.Add(this.CreateAutoIncrementOutputExpression(incrementParameters));
                }
                sqlstr = this.CreateSqlStatement(strStatements.ToArray());
                this.Database.ExecuteTimeout = this.TimeOut;
                this.OnCreated(sqlstr, paraDict);
                rowcount = this.Database.ExecuteNonQuery(sqlstr, parameters.ToArray());
                if (rowcount > 0)
                {
                    foreach (DbParameter par in parameters.Where(p => p.Direction == ParameterDirection.InputOutput))
                    {
                        PropertyInfo property = type.GetProperty(par.SourceColumn);
                        if (property == null)
                            continue;
                        property.SetValue(entity, par.Value);
                    }
                }
                this.OnExecuted(sqlstr, paraDict);
            }
            catch (Exception e)
            {
                this.OnErrorHandled(sqlstr, paraDict, e);
            }
            return rowcount;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public virtual int ExecuteUpdate(TEntity entity)
        {
            int rowcount = 0;
            string sqlstr = null;
            Dictionary<string, object> paraDict = new Dictionary<string, object>();
            try
            {
                ITableMapping tableMapping = TableMapping.GetTableMapping<TEntity>();
                string tableName = FormatDatabaseObject(tableMapping.SourceTable);

                List<string> colsets = new List<string>();
                List<string> conditions = new List<string>();
                List<DbParameter> parameters = new List<DbParameter>();
                Type type = typeof(TEntity);

                foreach (var col in tableMapping.ColumnMappings)
                {
                    ColumnMapping colmapping = (ColumnMapping)col;
                    string propertyName = colmapping.DataSetColumn;
                    string paramName = this.Database.ParameterPrefix + propertyName;
                    PropertyInfo property = type.GetProperty(propertyName);
                    object val = property.GetValue(entity);
                    DbParameter par = this.Database.CreateParameter(paramName, propertyName, property.PropertyType, System.Data.ParameterDirection.Input);
                    par.Value = val;
                    if (!colmapping.PrimaryKey)
                    {
                        colsets.Add(FormatKeyValueParameter(colmapping.SourceColumn, par.ParameterName));
                    }
                    else
                    {
                        conditions.Add(FormatKeyValueParameter(colmapping.SourceColumn, par.ParameterName));
                    }
                    parameters.Add(par);
                    paraDict.Add(par.ParameterName, par.Value);
                }
                sqlstr = this.CreateSqlStatement(string.Format(DatabaseUtil.UPDATE_COMMAND, tableName, string.Join(",", colsets.ToArray()), string.Join(" AND ", conditions.ToArray())));

                this.OnCreated(sqlstr, paraDict);
                this.Database.ExecuteTimeout = this.TimeOut;
                rowcount = this.Database.ExecuteNonQuery(sqlstr, parameters.ToArray());

                this.OnExecuted(sqlstr, paraDict);
            }
            catch (Exception e)
            {
                this.OnErrorHandled(sqlstr, paraDict, e);
            }
            return rowcount;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public virtual int ExecuteDelete(TEntity entity)
        {
            int rowcount = 0;
            string sqlstr = null;
            Dictionary<string, object> paraDict = new Dictionary<string, object>();

            try
            {
                ITableMapping tableMapping = TableMapping.GetTableMapping<TEntity>();
                string tableName = FormatDatabaseObject(tableMapping.SourceTable);

                List<string> conditions = new List<string>();
                List<DbParameter> parameters = new List<DbParameter>();
                Type type = typeof(TEntity);

                foreach (var cmapping in tableMapping.ColumnMappings)
                {
                    ColumnMapping colmapping = cmapping as ColumnMapping;
                    if (colmapping == null || !colmapping.PrimaryKey)
                        continue;

                    string propertyName = colmapping.DataSetColumn;
                    string paramName = this.Database.ParameterPrefix + propertyName;
                    PropertyInfo property = type.GetProperty(propertyName);
                    object val = property.GetValue(entity);
                    DbParameter par = this.Database.CreateParameter(colmapping.DataSetColumn, colmapping.DataSetColumn, property.PropertyType, System.Data.ParameterDirection.Input);
                    par.Value = val;
                    conditions.Add(FormatKeyValueParameter(colmapping.SourceColumn, par.ParameterName));
                    parameters.Add(par);
                    paraDict.Add(par.ParameterName, par.Value);
                }
                sqlstr = this.CreateSqlStatement(string.Format(DatabaseUtil.DELETE_COMMAND, tableName, string.Join(" AND ", conditions.ToArray())));

                this.Database.ExecuteTimeout = this.TimeOut;
                this.OnCreated(sqlstr, paraDict);
                rowcount = this.Database.ExecuteNonQuery(sqlstr, parameters.ToArray());

                this.OnExecuted(sqlstr, paraDict);
            }
            catch (Exception e)
            {
                this.OnErrorHandled(sqlstr, paraDict, e);
            }
            return rowcount;
        }
    }

}
